--末端时效新逻辑明细
--时间：2022-07-16
--开发：侯文龙
insert overwrite table jms_dm.dm_terminal_sign_detail_new_dt partition(dt)
select date_format(final_plan_sign_time, 'yyyy-MM-dd') as final_plan_sign_date,--规划签收日期
       date_format(final_plan_sign_time, 'yyyy-MM-dd') as postpone_plan_sign_date,--顺延规划签收日期
       case when aging_sign_time is null and is_difficult = 1 then 25
            when aging_sign_time is null then 26
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type in('菜鸟','邮政') then 15   --转邮
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '快件签收' then 11
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 14
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 1 then 12
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 2 then 13
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type in('菜鸟','邮政') then 27
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '快件签收' then 21
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 24
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 1 then 22
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 2 then 23
            else 26 end aging_sign_count_type,--时效签收统计类型 注：11 准点-网点签收 12 准点-终端入库(桃花岛认证) 13 准点-终端入库(自助对接) 14 准点-代理点收入 21 延误-网点签收 22 延误-终端入库(桃花岛认证) 23 延误-终端入库(自助对接) 24 延误-代理点收入 25 延误-问题件 26 延误-其他
       case when actual_sign_time is null and is_difficult = 1 then 24
            when actual_sign_time is null then 25
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '快件签收' then 11
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '出库扫描' then 12
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '代理点收入扫描' then 13
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '快件签收' then 21
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '出库扫描' then 22
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '代理点收入扫描' then 23
            else 25 end actual_sign_count_type,--实际签收统计类型 注：11 准点-网点签收 12 准点-驿站/快递柜 13 准点-代理点收入 21 延误-网点签收 22 延误-驿站/快递柜 23 延误-代理点收入 24 延误-问题件 25 延误-其他
       case when aging_sign_time is null and is_difficult = 1 then 25
            when aging_sign_time is null then 26
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '快件签收' then 11
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 14
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 1 then 12
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 2 then 13
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '快件签收' then 21
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 24
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 1 then 22
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 2 then 23
            else 26 end postpone_aging_sign_count_type,--顺延时效签收统计类型 注：11 准点-网点签收 12 准点-终端入库(桃花岛认证) 13 准点-终端入库(自助对接) 14 准点-代理点收入 21 延误-网点签收 22 延误-终端入库(桃花岛认证) 23 延误-终端入库(自助对接) 24 延误-代理点收入 25 延误-问题件 26 延误-其他
       case when actual_sign_time is null and is_difficult = 1 then 24
            when actual_sign_time is null then 25
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '快件签收' then 11
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '出库扫描' then 12
            when actual_sign_time <= final_plan_sign_time and actual_sign_scan_type = '代理点收入扫描' then 13
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '快件签收' then 21
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '出库扫描' then 22
            when actual_sign_time > final_plan_sign_time and actual_sign_scan_type = '代理点收入扫描' then 23
            else 25 end postpone_actual_sign_count_type,--顺延实际签收统计类型 注：11 准点-网点签收 12 准点-驿站/快递柜 13 准点-代理点收入 21 延误-网点签收 22 延误-驿站/快递柜 23 延误-代理点收入 24 延误-问题件 25 延误-其他
       order_source_code,
       nvl(order_source_name,'其他') as order_source_name,
       nvl(goods_type_code,'bm000006') as goods_type_code,
       2 as is_postpone,--是否顺延 1 是 2 否
       billcode,--运单号
       nvl(end_manage_region_code,'-') as end_manage_region_code,--末端网点所属管理大区编码
       nvl(end_manage_region_name,'-') as end_manage_region_name,--末端网点所属管理大区名称
       nvl(end_agent_code,'-') as end_agent_code,--末端网点所属代理区编码
       nvl(end_agent_name,'-') as end_agent_name,--末端网点所属代理区名称
       nvl(end_provider_id,0) as end_provider_id,--末端网点所属省份id
       nvl(end_provider_name,'-') as end_provider_name,--末端网点所属省份名称
       nvl(end_city_id,0) as end_city_id,--末端网点所属城市id
       nvl(end_city_name,'-') as end_city_name,--末端网点所属城市名称
       nvl(end_area_id,0) as end_area_id,--末端网点所属区县id
       nvl(end_area_name,'-') as end_area_name,--末端网点所属区县名称
       nvl(end_franchisee_code,'-') as end_franchisee_code,--末端网点所属加盟商编码
       nvl(end_franchisee_name,'-') as end_franchisee_name,--末端网点所属加盟商名称
       nvl(end_center_code,'-') as end_center_code,--末端发件中心/集散编码
       nvl(end_center_name,'-') as end_center_name,--末端发件中心/集散名称
       nvl(end_center_send_time,'-') as end_center_send_time,--末端中心/集散发件时间
       nvl(final_sign_network_code,'-') as final_sign_network_code,--最终签收派件网点编码
       nvl(final_sign_network_name,'-') as final_sign_network_name,--最终签收派件网点名称
       final_plan_sign_time,--规划签收时间
       final_plan_sign_time as postpone_plan_sign_time,--顺延规划签收时间
       planned_delivery_time,--规划发件时间
       deadline_signing_time,--规划签收截止时间
       (nvl(span_days_arrive,0) + nvl(out_line_day,0) + nvl(span_days_shift,0)) as span_days,--加时天数
       null as postpone_days,--顺延天数
       null as postpone_effective_time,--顺延生效时间
       null as postpone_expiration_time,--顺延失效时间
       deliver_shift,--派件派仓班次
       case when end_network_arrival_time >= end_center_send_time then end_network_arrival_time else null end end_network_arrival_time,--末端网点到件时间
       deliver_time,--出仓时间
       nvl(final_sign_user_code,'-') as final_sign_user_code,--最后签收派件员编码
       nvl(final_sign_user_name,'-') as final_sign_user_name,--最后签收派件员名称
       deliver_township_name,--出仓乡镇名称
       collect_in_time,--入库扫描时间
       terminal_type,--对接终端类型 驿站or柜机
       terminal_source,--对接终端来源 快宝驿站、丰巢等
       terminal_name,--对接终端名称、地址等
       collect_out_time,--出库扫描时间
       proxy_in_network_name,--代理点收入网点名称
       proxy_in_time,--代理点收入扫描时间
       deliver_out_time,--快件取出扫描时间
       sign_time,--快件签收扫描时间
       difficult_time,--问题件扫描时间
       difficult_type,--问题件类型
       case when aging_sign_time >= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00')  and aging_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59') then 1 else 2 end is_aging_sign_24,--是否22-24点前时效签收 注: 1 是 2 否
       case when actual_sign_time >= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00')  and actual_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59') then 1 else 2 end is_actual_sign_24,--是否24点前实际签收 注: 1 是 2 否
       case when aging_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59') then 1 else 2 end is_postpone_aging_sign_24,--是否24点前顺延时效签收 注: 1 是 2 否
       case when actual_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59') then 1 else 2 end is_postpone_actual_sign_24,--是否24点前顺延实际签收 注: 1 是 2 否
       case when actual_sign_time is not null then '完结'
            when actual_sign_time is null and difficult_first_type  in ('不可抗拒因素','客户地址无法进入','节假日无人收件') then '问题件'
            when actual_sign_time is null and terminal_type is not null and deliver_out_time is null then '第三方对接'
            when actual_sign_time is null and terminal_type is not null and deliver_out_time is not null then '快件取出'
            when actual_sign_time is null and deliver_time is not null then '收派员派件'
            else '其他' end end_type,--运单完结类型
       dt as end_center_send_date,--末端中心/集散发件日期
       case when actual_sign_time is not null then '完结'
            when actual_sign_time is null and deliver_time is not null then '网点出仓'
            when actual_sign_time is null and end_network_arrival_time is not null and end_network_arrival_time >= end_center_send_time then '网点到件'
            else '中心发件' end end_duty_type,--运单完结责任类型
       difficult_first_type,--问题件一级类型
       actual_sign_time,       --实际签收时间
       case when aging_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00') then 1 else 2 end  as is_aging_sign_22,
       case when aging_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59') then 1 else 2 end as is_aging_sign_00,
       aging_sign_time,--时效签收时间
        aging_sign_scan_type,--时效签收扫描类型
       case when (end_network_source = '共配' or deliver_source = '共配' or sign_source = '共配') then 1 else 2 end is_common,--是否共配
       third_code,   --三段码
       special_extra_time, --乡镇加时
       is_arrival_send,    --是否到出一体
       express_type_code,  --产品类型code
       express_type_name,   --产品类型名字
       actual_sign_scan_type,   --实际签收类型
       third_name,  --三段码名字
       if(four_in_one=1,end_brand_code,null) as end_brand_code,  --共配code
       if(four_in_one=1,end_brand_name,null) as end_brand_name,  -- 共配名字
       pick_network_code               --寄件网点编码
      ,pick_network_name               --寄件网点名称
      ,pick_agent_code                 --寄件网点所属代理区编码
      ,pick_agent_name                 --寄件网点所属代理区名称
      ,pick_provider_id                --寄件网点所属省份id
      ,pick_provider_name              --寄件网点所属省份名称
      ,pick_city_id                    --寄件网点所属城市id
      ,pick_city_name                  --寄件网点所属城市名
    ,is_shaidan                      --是否筛单网点',
    ,is_delay                        --是否顺延网点',
    ,is_new_open                     --是否新开网点',
    ,is_special_report               --是否特殊上报网点'
     ,customer_code
     ,customer_name
     ,if(invoice_no like '%Entervillage%',1,0) as is_duoduo
    ,dt
from jms_dwd.dwd_terminal_sign_all_detail_new_dt
where dt between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
and is_reject_reback_transfer = 2
and is_end_piece = 2
and end_center_send_time <= nvl(actual_sign_time,'9999-99-99')
distribute by dt, abs(hash(billcode)) % 100 ;

insert overwrite table jms_dm.dm_day_network_retention_sum_real_time_dt partition (dt)
select
 final_sign_network_code,
       final_sign_network_name,
       floor(sum(deliver_sum)/count(1))    delver_sum,
       floor(sum(deliver_lte_21)/count(1))  as deliver_lte_21,
       floor(sum(deliver_gt_22)/count(1)) as deliver_gt_22,
       '{{ execution_date | cst_ds }}' as date_time,
       0 as deliver_sum_2,
       floor(sum(deliver_sum_2)/count(1)) as delver_sum_2,
       '{{ execution_date | cst_ds }}' as dt
from (
         select final_sign_network_code,
                final_sign_network_name,
                floor(sum(if(deliver_time <= '21:00:00', 1, 0))) as deliver_lte_21,
                floor(sum(if(deliver_time > '21:00:00', 1, 0)))  as deliver_gt_22,
                final_plan_sign_date,
                sum(if(deliver_time >= '19:00:00' and deliver_time <= '22:00:00', 1, 0))  as deliver_sum,
                count(1) as deliver_sum_2
         from (
                  select billcode,
                         final_sign_network_code,
                         final_sign_network_name,
                         final_plan_sign_date,
                         date_format(deliver_time, 'HH:mm:ss') as deliver_time
                  from jms_dm.dm_terminal_sign_detail_new_dt
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_sub('{{ execution_date | cst_ds }}', 8)
                    and final_plan_sign_date <= date_add('{{ execution_date | cst_ds }}', 1)
                    and final_plan_sign_date >= date_add('{{ execution_date | cst_ds }}', -5)
                    and (special_extra_time != 0
                      or special_extra_time is not null)
                    and date_format(deliver_time, 'HH:mm:ss') >= '18:00:00'
                    and date_format(deliver_time, 'HH:mm:ss') <= '22:00:00'
              ) T1
         group by final_sign_network_code, final_sign_network_name, final_plan_sign_date
     )  tmp
where tmp.deliver_sum >100
group by final_sign_network_code, final_sign_network_name
distribute by 1
;